import pandas as pd
import os
from pathlib import Path


def get_course_package_resource_ids_from_single_file(excel_file_path, sheet_name=None):
    """
    从单个Excel文件中筛选【资源类型】为【课程包】的数据，获取【资源id】的去重列表

    Parameters:
    excel_file_path: Excel文件路径
    sheet_name: 工作表名称，如果为None则读取第一个工作表

    Returns:
    tuple: (resource_ids_list, file_info_dict)
    """
    file_info = {
        'file_name': os.path.basename(excel_file_path),
        'total_rows': 0,
        'filtered_rows': 0,
        'unique_ids': 0,
        'status': 'success',
        'error_msg': None
    }

    try:
        # 读取Excel文件
        if sheet_name:
            df = pd.read_excel(excel_file_path, sheet_name=sheet_name)
        else:
            df = pd.read_excel(excel_file_path)

        file_info['total_rows'] = len(df)

        # 检查必要的列是否存在
        required_columns = ['资源类型', '资源id']
        missing_columns = [col for col in required_columns if col not in df.columns]

        if missing_columns:
            file_info['status'] = 'error'
            file_info['error_msg'] = f"缺少列: {missing_columns}"
            return [], file_info

        # 筛选【资源类型】为【课程包】的数据
        filtered_df = df[df['资源类型'] == '课程包']
        file_info['filtered_rows'] = len(filtered_df)

        if filtered_df.empty:
            return [], file_info

        # 获取【资源id】列并去重
        resource_ids = filtered_df['资源id'].dropna().unique().tolist()

        # 转换为整数（如果需要）
        try:
            resource_ids = [int(id_val) for id_val in resource_ids if pd.notna(id_val)]
        except (ValueError, TypeError):
            # 如果无法转换为整数，保持原始类型
            resource_ids = [str(id_val) for id_val in resource_ids if pd.notna(id_val)]

        file_info['unique_ids'] = len(resource_ids)

        return resource_ids, file_info

    except FileNotFoundError:
        file_info['status'] = 'error'
        file_info['error_msg'] = '文件不存在'
        return [], file_info
    except Exception as e:
        file_info['status'] = 'error'
        file_info['error_msg'] = str(e)
        return [], file_info


def get_course_package_resource_ids_from_multiple_files(excel_files, sheet_name=None):
    """
    从多个Excel文件中筛选【资源类型】为【课程包】的数据，获取【资源id】的去重列表

    Parameters:
    excel_files: Excel文件路径列表 或 文件夹路径（会自动查找Excel文件）
    sheet_name: 工作表名称，如果为None则读取第一个工作表

    Returns:
    dict: 包含合并结果和详细信息的字典
    """
    # 如果传入的是字符串，检查是文件夹还是单个文件
    if isinstance(excel_files, str):
        if os.path.isdir(excel_files):
            # 如果是文件夹，查找所有Excel文件
            folder_path = Path(excel_files)
            excel_files = list(folder_path.glob("*.xlsx")) + list(folder_path.glob("*.xls"))
            excel_files = [str(f) for f in excel_files]
        else:
            # 如果是单个文件，转换为列表
            excel_files = [excel_files]

    all_resource_ids = []
    file_details = []

    print(f"开始处理 {len(excel_files)} 个Excel文件...")
    print("=" * 70)

    for i, excel_file in enumerate(excel_files, 1):
        print(f"\n[{i}/{len(excel_files)}] 处理文件: {os.path.basename(excel_file)}")

        resource_ids, file_info = get_course_package_resource_ids_from_single_file(excel_file, sheet_name)
        file_details.append(file_info)

        if file_info['status'] == 'success':
            all_resource_ids.extend(resource_ids)
            print(f"  ✓ 总行数: {file_info['total_rows']}")
            print(f"  ✓ 课程包行数: {file_info['filtered_rows']}")
            print(f"  ✓ 唯一资源ID: {file_info['unique_ids']}")
        else:
            print(f"  ✗ 处理失败: {file_info['error_msg']}")

    # 对所有文件的结果进行去重
    unique_all_ids = list(set(all_resource_ids))

    # 统计信息
    total_files = len(excel_files)
    success_files = sum(1 for info in file_details if info['status'] == 'success')
    error_files = total_files - success_files

    result = {
        'resource_ids': unique_all_ids,
        'summary': {
            'total_files': total_files,
            'success_files': success_files,
            'error_files': error_files,
            'total_unique_ids': len(unique_all_ids),
            'total_raw_ids': len(all_resource_ids)
        },
        'file_details': file_details
    }

    return result


def print_summary_report(result):
    """
    打印处理结果的汇总报告
    """
    print("\n" + "=" * 70)
    print("处理结果汇总报告")
    print("=" * 70)

    summary = result['summary']
    print(f"文件处理统计:")
    print(f"  总文件数: {summary['total_files']}")
    print(f"  成功处理: {summary['success_files']}")
    print(f"  处理失败: {summary['error_files']}")

    print(f"\n资源ID统计:")
    print(f"  原始ID总数: {summary['total_raw_ids']}")
    print(f"  去重后总数: {summary['total_unique_ids']}")

    print(f"\n各文件详细信息:")
    for info in result['file_details']:
        status_icon = "✓" if info['status'] == 'success' else "✗"
        print(f"  {status_icon} {info['file_name']}: ", end="")
        if info['status'] == 'success':
            print(f"总行数={info['total_rows']}, 课程包={info['filtered_rows']}, 唯一ID={info['unique_ids']}")
        else:
            print(f"失败 - {info['error_msg']}")


def save_results_to_files(result, output_prefix='batch_result'):
    """
    将结果保存到多个文件
    """
    try:
        # 保存资源ID列表
        ids_file = f"{output_prefix}_resource_ids.txt"
        with open(ids_file, 'w', encoding='utf-8') as f:
            for id_val in result['resource_ids']:
                f.write(str(id_val) + ',')
        print(f"\n资源ID已保存到: {ids_file}")

        # 保存详细报告
        report_file = f"{output_prefix}_report.txt"
        with open(report_file, 'w', encoding='utf-8') as f:
            f.write("批量处理Excel文件报告\n")
            f.write("=" * 50 + "\n\n")

            f.write("处理汇总:\n")
            summary = result['summary']
            f.write(f"总文件数: {summary['total_files']}\n")
            f.write(f"成功处理: {summary['success_files']}\n")
            f.write(f"处理失败: {summary['error_files']}\n")
            f.write(f"原始ID总数: {summary['total_raw_ids']}\n")
            f.write(f"去重后总数: {summary['total_unique_ids']}\n\n")

            f.write("各文件详情:\n")
            for info in result['file_details']:
                f.write(f"文件: {info['file_name']}\n")
                f.write(f"  状态: {info['status']}\n")
                if info['status'] == 'success':
                    f.write(f"  总行数: {info['total_rows']}\n")
                    f.write(f"  课程包行数: {info['filtered_rows']}\n")
                    f.write(f"  唯一ID数: {info['unique_ids']}\n")
                else:
                    f.write(f"  错误信息: {info['error_msg']}\n")
                f.write("\n")

        print(f"详细报告已保存到: {report_file}")

    except Exception as e:
        print(f"保存文件时出错: {str(e)}")


def generate_sql_in_clause(resource_ids):
    """
    将资源ID列表转换为SQL的IN子句格式
    """
    if not resource_ids:
        return "IN ()"

    id_str = ",".join(str(id_val) for id_val in resource_ids)
    return f"IN ({id_str})"


# 使用示例
if __name__ == "__main__":
    # 方式1: 传入文件列表
    excel_files = [
        "D:\\project_file\\周末智学导出资源\\高一上.xlsx",
        "D:\\project_file\\周末智学导出资源\\高二上.xlsx"
    ]

    # 方式2: 传入文件夹路径（会自动查找所有Excel文件）
    # excel_files = "path/to/excel/folder"

    # 方式3: 传入单个文件
    # excel_files = "single_file.xlsx"

    print("开始批量处理Excel文件...")

    # 处理多个Excel文件
    result = get_course_package_resource_ids_from_multiple_files(excel_files)

    # 打印汇总报告
    print_summary_report(result)

    # 显示最终结果
    if result['resource_ids']:
        print(f"\n最终合并的资源ID列表 (前20个):")
        preview_ids = result['resource_ids'][:20]
        print(preview_ids)

        if len(result['resource_ids']) > 20:
            print(f"... 还有 {len(result['resource_ids']) - 20} 个ID")

        # 保存结果到文件
        save_results_to_files(result)

        # 生成SQL IN子句（可选）
        sql_clause = generate_sql_in_clause(result['resource_ids'])
        print(f"\nSQL IN子句: {sql_clause[:100]}...")

    else:
        print("\n未找到任何符合条件的资源ID")


# 高级用法示例:
def advanced_usage_examples():
    """
    高级用法示例
    """

    # 1. 处理指定文件夹下的所有Excel文件
    result1 = get_course_package_resource_ids_from_multiple_files("./excel_folder/")

    # 2. 处理特定工作表
    result2 = get_course_package_resource_ids_from_multiple_files(
        ["file1.xlsx", "file2.xlsx"],
        sheet_name="数据表"
    )

    # 3. 只获取资源ID，不要详细信息
    resource_ids_only = result1['resource_ids']

    # 4. 检查处理结果
    if result1['summary']['error_files'] > 0:
        print("有文件处理失败，请检查详细报告")

    return result1, result2